let express = require('express');
let fs = require("fs");
let path = require("path");
let sprintf = require('sprintf');
let conn = require('../public/javascripts/connect');
let multipart = require('connect-multiparty');
let judge_login = require('../public/javascripts/judge_login');
let parseExcel = require('../public/download/parseExcel');
let department = require('../public/download/department');
let router = express.Router();
let multipartMiddleware = multipart();
let randomstring = require('randomstring');//随机字符串
let pictureImg = "", pictureImgarr = [], Detailedarr = [];//1图片编号2图片中的数据明细
router.post('/shibieimgobj', async function (req, res) {
    let colName = "";
    //table_name同程管理_2018_12,validation_data抓取的核对数据,year,哪一年的数据month,哪一个月的数据,
    // 科目 Subject,  Detailed 明细
    //picturenum  图片编号
    //lineNumber  行号
    //end 如果一个业务没有结束传false,结束了传true
    console.log("req.body", req.body);
    let {table_name, validation_data, year, month, Subject, Detailed, picturenum, lineNumber, end} = req.body;
    pictureImg = table_name.split("_")[0] + '_' + year + '_' + month + Subject + picturenum,
        validation_data = Number(validation_data),
        Detailed = Number(Detailed);
    if (table_name.split("_")[1] == year) {
        colName = '当月抓取值';
    } else {
        colName = '去年同期抓取';
    }
    console.log('pictureImg', pictureImg);
    Detailedarr.push(Detailed);
    console.log("Detailedarr", Detailedarr);
    Detailed = Detailedarr.join(",");//图片数据数组转字符串
    console.log("Detailed", Detailed);
    pictureImgarr.push(pictureImg);
    pictureImgStr = pictureImgarr.join(",");//图片名称数组转字符串
    let sqlDataVal = `(select ${colName} from ${table_name} where 科目='${Subject}')`;
    if (Subject.indexOf('82项的和') != -1) {
        // SELECT SUM(去年同期抓取) FROM `钣喷中心_2019_1` WHERE `科目`='变动费合计' OR `科目`='固定费合计'
        sqlDataVal = `(select SUM(${colName}) from ${table_name} B where 科目='变动费合计' OR 科目='固定费合计')`;
    }
    let sql = `REPLACE INTO check_date(id,table_name,grabbed_data,validation_data,picture_path,科目,year,month,result,Detailed)
    VALUES(0,'${table_name}',${sqlDataVal},${validation_data},'${pictureImgStr}','${Subject}',${year},${month},0,'${Detailed}')`;
    console.log("sql", sql);
    console.log("end", end);
    if (end) {
        await new Promise((resP) => {
            conn.connection2.query(sql, function (err, result, filds) {
                if (err) {
                    console.log("REPLACE INTO ERROR", err);
                    return;
                }
                console.log("result", result);
                resP(result);
            })
        })
        Detailedarr = [];//数据库修改为清空数据重新记录下一个业务的数据
        pictureImgarr = [];//数据库修改为清空数据重新记录下一个业务的数据
    }
    // pictureImg = "";//上传完情况编号
    res.json({success: true});
})
router.post('/shibieimg', multipartMiddleware, async function (req, res) {
    let resPath = path.join(__dirname, '../public/images/shibie');//上传文件的路径
    let fn = randomstring.generate();
    console.log("req.files", req.files);

    let SuffixName = req.files.files.originalFilename.split(".")[1]
    console.log("SuffixName", SuffixName);
    if (pictureImg) {
        let imgName = pictureImg + ".jpg";
        console.log("imgName", imgName);
        fs.createReadStream(req.files.files.path)
            .pipe(fs.createWriteStream(sprintf(`${resPath}/${imgName}`, fn)))
            .on('close', async function () {
                res.send('上传成功');
            });
    } else {
        res.send('上传失败');
    }

});
module.exports = router;